<?php
//--------------getters
class cls_bill_get extends cls_bill_set {
	
/*	
* function: collect data for charting and exporting data to external files
* parameters as shown in the function
*/  
	public function collect_data($month, $year,$data=null){ //1-collectible 2-collection
		include_once 'db_conn.php';
		$criteria=$year."-".$month;
		$sql="select * from reporting where adlaw='{$criteria}' group by barangay asc";
		//$sql="select * from reporting where adlaw='{$criteria}' and barangay='{$barangay_name}'";
		$sql_qry=mysql_query($sql) or die(mysql_error());
			while($row=mysql_fetch_array($sql_qry)){
				$data_coordinates_value.=$row['collectibles'].",";        
			
		}
		return  $data_coordinates_value;
		//mysql_free_result($sql_qry);
		
		
	}
	
	public function collection_data($month, $year,$data=null){ //1-collectible 2-collection
		include_once 'db_conn.php';
		$criteria=$year."-".$month;
		$sql="select * from reporting where adlaw='{$criteria}' group by barangay asc";
		//$sql="select * from reporting where adlaw='{$criteria}' and barangay='{$barangay_name}'";
		$sql_qry=mysql_query($sql) or die(mysql_error());
		
			while($row=mysql_fetch_array($sql_qry)){
			$data_coordinates_value.=$row['collection'].",";
		}
		return  $data_coordinates_value;
		//mysql_free_result($sql_qry);
		
		
	}
	
	
	public function last_OR_Used(){
		include_once 'db_conn.php';
		$sql_str="select * from codes where code='last_or_no'";
		try {
			mysql_query($sql_str);
			//below is for debug purposes only,delete after deployment
			throw new Exception('database error');
		}catch(Exception $e){
			echo "error on line ".$e->getLine();
		}
		//TODO:priority(high)dev:mike(comments:::add +1 on success retrieval of the last or no from the datbase 
	}
	
	//todo:[start]stage for commit to remote PS (coding status:uploaded)
	public function collection_today($month=null){//returns the collection for the day
		include_once 'db_conn.php';
		$Year_Now=date('Y');
		if($month==null || $month==''){
			$sql_str1="select * from or_log where or_date=date(now())";    
		}elseif($month=='all'){
			$criteria=$Year_Now."%";
			//echo "criteria=".$criteria;
			$sql_str1="select * from or_log where or_date like '{$criteria}'";
		}else{
			$criteria=$Year_Now."-".$month."%";
			$sql_str1="select * from or_log where or_date like '{$criteria}'";
						
		}
		
		$sql_qry1=mysql_query($sql_str1) or die(mysql_error());
		while($row1=mysql_fetch_array($sql_qry1)){
			$collection=$collection + $row1['issued_amnt'];
		}
		return  number_format($collection,2,".",",");
	}//returns the collection for the day
	//todo:[end]stage for commit to remote PS
	
	public function for_disconn(){//get accounts for disconnection notice
/*		include_once 'db_conn.php';
		include_once 'forms.php';
		//prepare for_disconn table for new entry
		@mysql_query("truncate table for_disconn");
		//continue process for the purpose of this function
		$sql_str1="select * from disconn_dates order by accnt_no asc";
		$sql_qry1=mysql_query($sql_str1) or die(mysql_error());
		while($row1=mysql_fetch_array($sql_qry1)){ //iterate all dates for disconnection notice
			$date1=$row1['disconn_date'];
			$account_no=$row1['accnt_no'];
			$reading_date=$row1['reading_date'];
			$date_distance=cls_bill_get::date_diff('current_date',$date1);
			if($date_distance >= 0){ //for disconnection found
				//check existence of the variables in the statement below before inserting data
				$sql_str1_1="select * from for_disconn where accnt_no='{$account_no}' and reading_date='{$reading_date}'";
				$sql_qry1_1=mysql_query($sql_str1_1) or die(mysql_error());
				if($cnt_row1_1=mysql_numrows($sql_qry1_1)==0){ //duplication not found, continue insertion
					$sql_str2="insert into for_disconn(accnt_no,reading_date)values('{$account_no}','{$reading_date}')";
					$sql_qry2=mysql_query($sql_str2) or die(mysql_error()); //insert into table for listings    
				}
			}
		} //iteration ends here
		//recount account number with greater than 2 reading-dates
		$sql_str3="select accnt_no, count(accnt_no) as cnt_reslt from for_disconn group by accnt_no having count(accnt_no) > 1";
		$sql_qry3=mysql_query($sql_str3) or die(mysql_error());
		//reiterate here and those that account number where count values is greater than two will be listed for disconnection notice
		?>
		<table align="center" border="0" cellpadding="0" cellspacing="0" width="auto">
		<tr><td align="center"><strong>Accounts</strong></td><td align="center"><strong>Bills</strong></td></tr>
		<!--<tr><td colspan="2">&nbsp;</td></tr>-->
		<?php
		while($row2=mysql_fetch_array($sql_qry3)){
			//$iteration_numer=$iteration_numer + 1;
			//if($row2['cnt_reslt']>= 2){ //create notice for disconnection
			//TODO:present the number of bills in hover or tooltip
			?><tr><td><?php echo $row2['accnt_no']?></a></td><td align="center"><?php echo $row2['cnt_reslt']?></td></tr><?php	
			}
			//cls_forms::ProfileWindow($row2['accnt_no'],$iteration_numer);
		//}?></table><?php
	*/	
	}
																					  
	public function for_disconn1(){ //revision 2.0
		/*
		* 1. get all account numbers
		* 2. iterate from the array of numbers and convert account numbers to ledger table format
		* 3. iterate within the table for possible disconnection notice where had not been paid or no activity for its OR_num column
		*/
		
		//TODO: before executing codes below check the last update of table for_disconn to prevent redundant execution and preserve resources
		
		
		//prevent script timeout execution
		set_time_limit(0);
		//set_memory_limit(-1);
		
		include_once 'cls_user.php';
		include_once 'cls_codes.php';
		$account_nos=cls_user_get::all_concessionaires_account_no();
		$date_now=date('Y-m-d');
		
		//prepare the table for_disconn for updates
		$sql_str="truncate table for_disconn";
		$sql_qry=mysql_query($sql_str) or die(mysql_error());
		
		//start iterating to array
		for($i=0;$i< count($account_nos);$i++){
			$LedgerTable="ldg_".cls_misc::sanitize_hyp($account_nos[$i]); //acount number converted to ledger table format
			//get all billing period where they have not been paid or no activity at all
			$sql_str="select * from {$LedgerTable} where OR_num='' or OR_num is null order by reading_date asc";
			$sql_qry=mysql_query($sql_str) or die(mysql_error());
			while($row=mysql_fetch_array($sql_qry)){
				//get the disconnection date of the current row
				$DisconnDate=cls_bill_get::disconn_date(cls_bill_get::payment_date($row['reading_date'],$LedgerTable));
				if($date_now > $DisconnDate){ //due date bill found
					 if(cls_misc::CheckDuplication('for_disconn','accnt_no','reading_date',$account_nos[$i],$row['reading_date'])=='0'){ //no duplication found
						//insert into for_disconn table
						$str_update_tblForDisconn="insert into for_disconn(accnt_no,reading_date)values('{$account_nos[$i]}','{$row['reading_date']}')";
						//execute statement
						$qry_update_tblForDisconn=mysql_query($str_update_tblForDisconn) or die(mysql_error());
					 }
				}
			}	
		}
		  //retrieve all the data from table for_disconn
		  //TODO:[start] stage for commit to remote PS (uploaded)
		  ?><table width="80%"><tr><td>Accnt. No</td><td>Unpaid </td></tr> <?php
		  //TODO:[end] stage for commit to remote PS
		  $DisConnAccount_nos=cls_user_get::ForDisconnectionNotice();
		  
		  foreach($DisConnAccount_nos as $key=>$value){
			echo "<tr><td>{$value['accnt_no']}</td><td align=\"center\">{$value['count']}</td></tr>";
		  }
		  ?></table><script type="text/javascript">//alert('script done')</script> <?php
	}
	
	public function compute_bill_amount($account_no,$billing_date,$cu_used,$connection_type){
		include_once 'db_conn.php';
		include_once 'cls_user.php';
		include_once 'cls_codes.php';
		switch ($connection_type) {
			case '1'://residential
				if($cu_used >= 31){
					$bill_amnt=number_format(((($cu_used - 30) * 17) + 160.00 + 150.00 + 140.00),2,'.',',');
				}elseif($cu_used <= 30 && $cu_used > 20 ){
					$bill_amnt=number_format(((($cu_used - 20) * 16.00) + 150.00 + 140.00),2,'.',',');
				}elseif($cu_used < 21 && $cu_used > 10){
					$bill_amnt=number_format(((($cu_used -10) * 15.00) + 140.00),2,'.',',');
				}elseif ($cu_used <= 10){
					$bill_amnt=number_format(140.00,2,'.',',');
				}
				/*	
				if ($cu_used <= 10){//} ||$cu_used > 0) {
					?><script type="text/javascript">alert('line 115 invoked')</script><?php
					$bill_amnt=number_format(140.00,2,'.',',');
				}elseif ($cu_used > 10 || $cu_used < 21){
					?><script type="text/javascript">alert('line 118 invoked')</script><?php
					$bill_amnt=number_format(((($cu_used -10) * 15.00) + 140.00),2,'.',',');
				}elseif ($cu_used > 20 || $cu_used <= 30){
					?><script type="text/javascript">alert('line 119 invoked')</script><?php
					$bill_amnt=number_format(((($cu_used - 20) * 16.00) + 150.00 + 140.00),2,'.',',');
				}elseif ($cu_used >= 31){
					?><script type="text/javascript">alert('line 123 invoked')</script><?php
					$bill_amnt=number_format(((($cu_used - 30) * 17) + 160.00 + 150.00 + 140.00),2,'.',',');
					//$bill_amnt=number_format(((($cu_used - 30) * 17.00) + 450),2,'.',',');
				}*/
				
				break;

			case '2'://commercial
				
				if($cu_used >= 31){
					$bill_amnt=number_format(((($cu_used - 30) *25.50) + 240 + 225.00 + 210.00),2,'.',',');
				}elseif($cu_used <= 30 && $cu_used > 20 ){
					$bill_amnt=number_format(((($cu_used - 20) * 24.00) + 225.00 + 210.00),2,'.',',');
				}elseif($cu_used < 21 && $cu_used > 10){
					$bill_amnt=number_format(((($cu_used -10) * 22.50) + 210.00),2,'.',',');
				}elseif ($cu_used <= 10){
					$bill_amnt=number_format(210.00,2,'.',',');
				}
							
/*				
				if ($cu_used < 11 ||$cu_used > 0) {
					$bill_amnt=number_format(210.00,2,'.',',');
				}elseif ($cu_used > 10 || $cu_used < 21){
					$bill_amnt=number_format(((($cu_used -10) * 22.50) + 210.00),2,'.',',');
				}elseif ($cu_used > 20 || $cu_used < 31){
					$bill_amnt=number_format(((($cu_used - 20) * 24.00) + 225.00 + 210.00),2,'.',',');
				}elseif ($cu_used > 30){
					$bill_amnt=number_format(((($cu_used - 30) *25.50) + 240 + 225.00 + 210.00),2,'.',',');
				}
*/				break;
		}
		//return data
		return $bill_amnt;
	}
	
	public function total_bill_amount($account_no,$billing_date){
		include_once 'db_conn.php';
		include_once 'cls_codes.php';
		$accnt_no=cls_misc::sanitize_hyp($account_no);
		$sql_str="select * from ldg_{$accnt_no} where reading_date='{$billing_date}'";
		$sql_qry=mysql_query($sql_str) or die(mysql_error());
		$row=mysql_fetch_array($sql_qry);
		$total=$row['bill_amnt'] + $row['loans_MLP'] + $row['loans_MF'];
		//set total value to customer ledger
		parent::Total($accnt_no,$billing_date,$total);

	}

	public function payment_date($reading_date,$account_number){ //returns the payment date
		include_once 'db_conn.php';
		include_once 'date_time.php';
		include_once 'cls_user.php';
		$const_date=cls_user_get::const_payment_date($account_number);
		$month=cls_date_get::extract_month($reading_date);
		$year=cls_date_get::extract_year($reading_date);
		$new_date= $year.'-'.$month.'-'.$const_date;
		$sql_str="select date_add('{$new_date}',interval 1 month) as new_date";
		$sql_qry=mysql_query($sql_str) or die(mysql_error());
		$row=mysql_fetch_array($sql_qry);
		return $row['new_date'];
	}

	public function payment_grace($payment_date){ //returns the grace period for the payment
		$sql_str="select date_add('{$payment_date}',interval 15 day) as grace_period";
		$sql_qry=mysql_query($sql_str) or die(mysql_error());
		$row=mysql_fetch_array($sql_qry);
		return $row['grace_period'];
	}

	public function payment_ext($payment_date){ //returns the payment extensions
		$sql_str="select date_add('{$payment_date}',interval 20 day) as extension";
		$sql_qry=mysql_query($sql_str) or die(mysql_error());
		$row=mysql_fetch_array($sql_qry);
		return $row['extension'];
	}

	public function disconn_date($payment_date){ //returns the disconnection dates
		$sql_str="select date_add('{$payment_date}',interval 21 day) as disconn_date";
		$sql_qry=mysql_query($sql_str) or die(mysql_error());
		$row=mysql_fetch_array($sql_qry);
		return $row['disconn_date'];
	}

	public function date_diff($recent,$old){ //returns the difference of two dates
		//$sql_str="select datediff('{$recent}','{$old}') as result";
		$sql_str="select datediff(date(now()),'{$old}') as result";
		$sql_qry=mysql_query($sql_str) or die(mysql_error());
		$row=mysql_fetch_array($sql_qry);
		return $row['result'];
	}

	public function unsettled_bills($account){
		include_once 'db_conn.php';
		include_once 'cls_codes.php';
		include_once 'date_time.php';
		?>
		<script type="text/javascript">//alert('please settle the oldest bill first');</script>
		<?php
		$ledger=cls_misc::sanitize_hyp($account);
		//$sql_str="select * from ldg_{$ledger} where isNAN()OR_num='0' or OR_num is null order by reading_date asc";
		$sql_str="select * from ldg_{$ledger} where OR_num='' or OR_num is null order by reading_date asc";
		$sql_qry=mysql_query($sql_str) or die(mysql_error());
		?>
		<br>
		<center>
		<form name="form1" method="POST" action="form_handler.php">
		<input type="hidden" name="cashier" value="to_settle">
		<input type="hidden" name="accnt_no" value="<?php echo base64_encode(trim($account))?>">
		<table>
			<tr><td>No. of Unsettled Bills:<strong><?php echo mysql_num_rows($sql_qry)?></strong></td></tr>
		</table>
		<br>
		<table>
			<tr><th colspan="5">Select Billing Month to Settle</th></tr>
			<tr>
				<td>Billing Month</td>
				<td>Due Date</td>
				<td>Amount</td>
				<td>Penalty(5%)</td>
				<td>Total Amount</td>
			</tr>
		<?php
		while ($row=mysql_fetch_array($sql_qry)) {
		?>
		<tr><!--<b>Billing Month</b>!-->
			<?php  $billing_month=cls_misc::toString(cls_date_get::extract_month($row['reading_date']),'month').'-'. cls_date_get::extract_year($row['reading_date'])?>
			<?php $amount_to_settle=number_format($row['total'] + cls_bill_get::compute_penalty($row['reading_date'],$row['total'],trim($account)),2,'.',',')?>
			<?php $penalty=cls_bill_get::compute_penalty($row['reading_date'],$row['total'],$account)?>
			<td><a href="cashier.php?request=to_settle&accnt_no=<?php echo base64_encode(trim($account))?>&bill_date=<?php echo base64_encode($row['reading_date'])?>&amnt=<?php echo base64_encode($amount_to_settle)?>&penalty=<?php echo base64_encode($penalty)?>" title="<?php echo $billing_month?>"><b><?php echo $billing_month?></b></a> </td>
			<!--due date!-->
			<td><?php echo cls_date_get::MM_DD_yyyy_format(cls_bill_get::payment_ext(cls_bill_get::payment_date($row['reading_date'],trim($account))))?>
			<?php //echo cls_bill_get::payment_ext(cls_bill_get::payment_date($row['reading_date'],trim($account)))?> </td>
			<!--amount!-->
			<td><?php echo $row['bill_amnt']?></td>
			<!--penalty!-->
			<td> <?php echo cls_bill_get::compute_penalty($row['reading_date'],$row['total'],$account)?> </td>
			<!--total payment to date plus penalty if ever!-->
			<td><?php echo number_format($row['total'] + cls_bill_get::compute_penalty($row['reading_date'],$row['total'],trim($account)),2,'.',',')?></td>
		</tr>
		<?php
		}
		?>
		</table>
		</form>
		</center>
		<?php
	}

	public function compute_penalty($reading_date,$amount,$account_number){
		include_once 'cls_bill.php';
		$penalty_percentage=0.05;
		$date_diff=cls_bill_get::date_diff('current_date',cls_bill_get::payment_ext(cls_bill_get::payment_date($reading_date,$account_number)));
		//echo "date_different_results=". $date_diff;
		if ($date_diff >= 1) {
			$penalty_amount=$penalty_percentage * $amount;
			return number_format($penalty_amount,2,'.',',');
			//TODO:penalty computation required
		}else{
			return 0;
		}
	}

	public function last_bill_created($account_no){
		include_once 'db_conn.php';
		include_once 'cls_codes.php';
		$accnt_no=cls_misc::sanitize_hyp(trim($account_no));
		$sql_str="select * from ldg_{$accnt_no} where OR_num='0' or OR_num is null order by reading_date desc";
		$sql_qry=mysql_query($sql_str) or die(mysql_error());
		$row=mysql_fetch_array($sql_qry);
		return $row['reading_date'];
	}

	public function bool_dl($accnt_no,$reading_date){
		include_once 'db_conn.php';
		$sql_str="select * from dl_bill where accnt_no='{$accnt_no}' and last_bill='{$reading_date}'";
		$sql_qry=mysql_query($sql_str) or die(mysql_error());
		if (mysql_num_rows($sql_qry)>=1) {
			$msg="yes";
		}else{
			$msg="no";
		}
		return $msg;
	}
	public function check_if_exist_in_discon($account_no,$reading_date,$disconn_date){
		$str = mysql_query("select * from disconn_dates where accnt_no='".$account_no."' and reading_date='".$reading_date."'") or die("error".mysql_error());
		$str = mysql_num_rows($str);
		if($str==0){
			mysql_query("insert into disconn_dates(accnt_no,reading_date,disconn_date)values('".$account_no."','".$reading_date."','".$disconn_date."')") or die("error in inserting to disconne".mysql_error());
		} else{
		  #no operation
		 
		}
		
	}
	public function write_to_disconn_table(){
		#this will insert accountno to disconn dates tabble
	   $mga_accounts = array();  //creates an array to store account no                                   
	   $str = mysql_query("select * from profile") or die('erri'.mysql_error());
	   while($row=mysql_fetch_assoc($str)){
	   array_push($mga_accounts,$row['acct_no']);
	   }
	   #this will iterate to each table
	   foreach($mga_accounts as $accountno){
		   $str = mysql_query("select * from ldg_".str_replace("-","_",$accountno)." where OR_date is null or OR_date=''" );
		   while($row=mysql_fetch_assoc($str)){
			   #this will get the disconn_date(payment date)
			   $disconn_date =  cls_bill_get::disconn_date(cls_bill_get::payment_date($row['reading_date'],$accountno)); 
			   #this will check if the reading date for acount no exists in table discnonn then insert
					cls_bill_get::check_if_exist_in_discon($accountno,$row['reading_date'],$disconn_date);
			   }
	   }
	}

}

//----------------setters
class cls_bill_set{
	
	public function setup_OR($first_OR_num,$last_OR_num){//used to setup OR numbers for series of receipts for cashier department only
		include_once 'db_conn.php';
		$sql_str="update codes set descr='{$first_OR_num}' where code='or_start'";
		try{
			mysql_query($sql_str);
			//below is for debug for purposes only,suppress mysql_query error after deployment
			throw new Exception(mysql_error());
		}catch(Exception $e){
			echo "error on ". $e->getLine();
		}
		$sql_str="update codes set descr='{$last_OR_num}' where code='or_stop'";
		try{
			mysql_query($sql_str);
			//below is for debug purposes only
			throw new Exception(mysql_error());
		}catch(Exception $e){
			echo "mysql_erron on line ". $e->getLine();
		}
	} //used to setup OR numbers for series of receipts for cashier department only

	protected  function Total($account_no,$billing_date,$total_value){
		include_once 'db_conn.php';
		include_once 'cls_codes.php';
		$ledger_no=cls_misc::sanitize_hyp($account_no);
		$sql_str="update ldg_{$ledger_no} set total='{$total_value}' where reading_date='{$billing_date}'";
		$sql_qry=mysql_query($sql_str) or die(mysql_error());
	}

	public function consumer_payment($account_no){

	}

	public function bill_form($account_no,$reading_date){
		include_once 'db_conn.php';
		include_once 'cls_codes.php';
		include_once 'date_time.php';
		include_once 'classes/PHPExcel.php';
		include_once 'classes/PHPExcel/IOFactory.php';

		//prepare the data that will be written to the excel file
		$ledger_table=cls_misc::sanitize_hyp(trim($account_no));
		$sql_str="select * from {$ledger_table} where reading_date='{$reading_date}'";
		$sql_qry=mysql_query($sql_str) or die(mysql_error());
		$row=mysql_fetch_array($sql_qry);


		//write the date to excel file

		//declare filetype
		$FileType='Excel5';
		//declare file template
		$file_template='accnts/template.xls';
		$file_output="accnts/{$account_no}.xls ";

		$objReader=PHPExcel_IOFactory::createReader($FileType);
		$objFileLoader=$objReader->load($file_template);
		$objWrkSheet=$objFileLoader->getActiveSheet();
		//TODO:commit changes to the file
		//present reading=cell(b10)
		$present_reading=$row['meter_reading'];
		$objWrkSheet->getCell('B10')->setValue("{$present_reading}");
		//usage amount=cell E10
		$cu_m_usage=$row['cu_used'];
		$objWrkSheet->getCell('E10')->setValue("{$cu_m_usage}");
		//previous reading=cell c10
		$previous_reading=$present_reading - $cu_m_usage;
		$objWrkSheet->getCell('C10')->setValue("{$previous_reading}");
		//meter fee=cell E12
		$meter_fee=$row['loans_MF'];
		$objWrkSheet->getCell('E12')->setValue("{$meter_fee}");
		//penalty=E13
		$penalty=$row['pen_fee'];
		$objWrkSheet->getCell('E13')->setValue("{$penalty}");
		//material loan program=E14
		$mlp_fee=$row['loans_MLP'];
		$objWrkSheet->getCell('E14')->setValue("{$mlp_fee}");
		//interest=E15
		//reconnection fee=E16
		//billing month=D20
		$billing_month=cls_misc::toString(cls_date_get::extract_month($row['reading_date']),'month')."-".cls_date_get::extract_year($row['reading_date']);
		$objWrkSheet->getCell('D20')->setValue("{$billing_month}");
		//consumer account number=D22
		$consumer_accnt=trim($account_no);
		$objWrkSheet->getCell('D22')->setValue("{$consumer_accnt}");

		//-------------prepare connection to profile
		$sql_str2="select * from profile where acct_no='{$consumer_accnt}'";
		$sql_qry2=mysql_query($sql_str2) or die(mysql_error());
		$row2=mysql_fetch_array($sql_qry2);

		//serial number=D23
		$serial_no=$row['serial_no'];
		$objWrkSheet->getCell('D23')->setValue("{$serial_no}");
		//account holder=C25
		$acct_name=$row['applicant'];
		$objWrkSheet->getCell('C25')->setValue("{$acct_name}");
		//account holder address=C26
		$brgy_add=cls_misc::toString($row['address_brgy'],'Barangay');
		$objWrkSheet->getCell('C26')->setValue("{$brgy_add}");
		//payment date=D28
		$payment_date=cls_date_get::MM_DD_yyyy_format(cls_bill_get::payment_date($reading_date,$consumer_accnt));
		$objWrkSheet->getCell('D28')->setValue("{$payment_date}");
		//prepared by=D30
		//TODO:derived username from the cookies and cross check with the user credentials
		///TODO:start modifying excel for output


		//check if file exist
		if (var_dump(unlink($file_output))==false) {
			$objWriter=PHPExcel_IOFactory::createWriter($objFileLoader,$FileType);
			$objWriter->save($file_output);
		}
		//unset($objReader);
		//TODO:unset variables to free memory from cluttering
	
	} //function ends



	//TODO: [start] stage to commit to remote PS (code status: uploaded)
	public function reading_sheet_form($BarangayCode){ //return the filename of the output
		include_once 'db_conn.php';
		include_once 'cls_codes.php';
		include_once 'date_time.php';
		include_once 'classes/PHPExcel.php';
		include_once 'classes/PHPExcel/IOFactory.php';

		
		//local scope page variable
		$template_file="accnts/rs_template.xls";
		//output and download the excel as rs_yyyy_mm_brgy_code(all separated by _)
		$file_output="accnts/rs_".date('Y-m')."_".$BarangayCode.".xls";
		
		//get the list of concessionaires from profile where BarangayCode is the same as the parameter given
		$Sql_NoOfApplicant="select acct_no from profile where address_brgy='{$BarangayCode}'";
		$Qry_NoOfApplicant=mysql_query($Sql_NoOfApplicant) or die(mysql_error());
		//count the output rows and divide by 25
		//the quotient will corresponds to the number of worksheed that will be created
		$Pages=explode(".",mysql_num_rows($Qry_NoOfApplicant)/25);
		if(mysql_numrows($Qry_NoOfApplicant) % 25 !=0){
			$Min_NumOfPages=$Pages[0] + 1;
		}else{
			$Min_NumOfPages;
		}
		$Num_OfPages=$Min_NumOfPages;
		
		//get the Account numbers
		while($ApplicanstRetrieve=mysql_fetch_array($Qry_NoOfApplicant)){
			$AccntNoRetrieve[]=$ApplicanstRetrieve['acct_no'];
		}
		
		//echo "Test: total account number retrieve=".count($AccntNoRetrieve)."<br>";
		
		//declare filetype
		$FileType='Excel5';
		//initiate phpexcel algorithms and create necessary worksheets required to create reading sheets
		$objReader=PHPExcel_IOFactory::createReader($FileType);
		$objFileLoader=$objReader->load($template_file);
		$objWrkSheet=$objFileLoader->getActiveSheet();
		
		$no_start="1";$no_end="25";
		//create clone worksheets
		for($i=1;$i < $Num_OfPages;$i++){
			$no_start=$no_start + 25;
			$no_end=$no_end + 25;
			$objClone=clone $objWrkSheet;    
			//create temporary worksheet name
			$objClone->setTitle("P{$i}");
			//$objClone->setTitle("C{$no_start}-{$no_end}"); //use this later after populating data
			$objFileLoader->addSheet($objClone);
		}
		//start populating the worksheets
		$MaxSheetIndex=$Num_OfPages;
		$no_start=1;$no_end=25;
		$value=0;
		for($i=0;$i < $MaxSheetIndex;$i++){
			$Start_RecordSet=$no_start - 1;
			//$End_RecordSet=$no_end -1;
			$SQL_ReCreateForEachSheet="select * from profile where address_brgy='{$BarangayCode}' order by applicant asc limit {$Start_RecordSet},25";
			$e=new ErrorException();
			$QRY_ReCreateForEachSheet=mysql_query($SQL_ReCreateForEachSheet) or die(mysql_error($e->getFile()."-".$e->getLine()));
			while($ROW_ReCreateForEachSheet=mysql_fetch_array($QRY_ReCreateForEachSheet)){
				$AccountNo_Values[]=$ROW_ReCreateForEachSheet['acct_no'];
				$AccountName_Values[]=$ROW_ReCreateForEachSheet['applicant'];
				$AccountMeterNo_Values[]=$ROW_ReCreateForEachSheet['meterno'];
			}
			
			$ActiveSheet=$objFileLoader->setActiveSheetIndex($i);
			$ActiveSheet->SetCellValue("A6","Barangay: ".cls_misc::toString($BarangayCode,"Barangay")."      Month: ".cls_misc::toString(date('m'),'month')."-".date('Y'));
			$objFileLoader->setActiveSheetIndex($i);
			$start_row=9;$stop_row=$start_row + 25;
			while($start_row < $stop_row){
				$ActiveSheet->setCellValue("A".$start_row,$value+1);
				$ActiveSheet->setCellValue("B".$start_row,$AccountNo_Values[$value]);
				$ActiveSheet->setCellValue("C".$start_row,$AccountName_Values[$value]);
				$ActiveSheet->setCellValue("D".$start_row,$AccountMeterNo_Values[$value]);
				//get the last reading value on each ledger and get the 
				if($AccountNo_Values[$value]!=null||$AccountNo_Values[$value]!=''){
				$LedgerTable=cls_misc::ConvertToTableName($AccountNo_Values[$value]);
//				echo "Test: Ledger Table=".$LedgerTable."<br>";
				$SQL_RetLastMeterReading="select * from {$LedgerTable} order by reading_date desc limit 0,1";
				$QRY_RetLastMeterReading=mysql_query($SQL_RetLastMeterReading) or die(mysql_error($e->getFile()."-".$e->getLine()));
				$ROW_RetLastMeterReading=mysql_fetch_array($QRY_RetLastMeterReading);
				$ActiveSheet->setCellValue("E".$start_row,$ROW_RetLastMeterReading['meter_reading']);
				}
				$value++;
				$start_row++;
			}
			$ActiveSheet->setTitle("C{$no_start}-{$no_end}");
			$no_start=$no_start + 25;
			$no_end=$no_start + 25-1;
		}
		
		$objFileLoader->setActiveSheetIndex(0);
		//check if file exist
		if(file_exists($file_output)){
			unlink($file_output);
		}
		//proceed to output creation
		$objWriter=PHPExcel_IOFactory::createWriter($objFileLoader,$FileType);
		$objWriter->save($file_output);
		unset($objReader);
		//return void;
//TODO: Priority-High insert filename of the newly creaded reading worksheet to table name for reference purposes only.
	} //function ends
	
}//class ends
?>